package com.emuporium.gymrat.server.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class ProgramDB {
	public static boolean addRoutine(int routineId, int programId, String note) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;

		String query = "insert into programentries (programid, routineid, note) "
				+ "values (?,?,?)";

		try {
			ps = connection.prepareStatement(query);
			ps.setInt(1, programId);
			ps.setInt(2, routineId);
			ps.setString(3, note);
			if (ps.executeUpdate() > 0)
				return true;

		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}
		return false;
	}

	public static ArrayList<Program> getProgramsWithRoutine(int routineId,
			int userId) {
		// ConnectionPool pool = ConnectionPool.getInstance();
		// Connection connection = pool.getConnection();
		// PreparedStatement ps = null;
		// ResultSet rs = null;
		// ArrayList<Program> programs = new ArrayList<Program>();
		//
		// String query = "select * from programs where";
		// String query2 = "select last_insert_id()";
		//
		// try {
		// ps = connection.prepareStatement(query);
		//
		// } catch (SQLException e) {
		// e.printStackTrace();
		//
		// } finally {
		// DBUtil.closeResultSet(rs);
		// DBUtil.closePreparedStatement(ps);
		// pool.freeConnection(connection);
		// }
		return null;
	}

	public static Program getProgram(int programId) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSet innerRs = null;
		Program program = null;

		String query = "select * from programs where _id=?";
		String query2 = "select * from programentries where programid = ?";

		try {
			ps = connection.prepareStatement(query);
			ps.setInt(1, programId);
			rs = ps.executeQuery();

			if (rs.next()) {
				program = new Program();
				program.setId(rs.getInt("_id"));
				program.setName(rs.getString("name"));
				program.setDescription(rs.getString("description"));
				program.setAuthor(rs.getString("author"));
				program.setShared(rs.getBoolean("shared"));
				program.setSourceId(rs.getInt("sourceid"));

				ps = connection.prepareStatement(query2);
				ps.setInt(1, programId);
				innerRs = ps.executeQuery();

				while (innerRs.next()) {
					Routine r = RoutineDB.selectRoutine(innerRs
							.getInt("routineid"));
					r.setProgramNote(innerRs.getString("note"));
					program.getRoutines().add(r);
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}
		return program;
	}

	public static ArrayList<Program> getPrograms(int userId) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSet innerRs = null;
		ArrayList<Program> programs = new ArrayList<Program>();

		String query = "select * from programs where ownerid=?";
		String query2 = "select * from programentries where programid = ?";

		try {
			ps = connection.prepareStatement(query);
			ps.setInt(1, userId);
			rs = ps.executeQuery();

			while (rs.next()) {
				Program p = new Program();
				p.setId(rs.getInt("_id"));
				p.setName(rs.getString("name"));
				p.setDescription(rs.getString("description"));
				p.setAuthor(rs.getString("author"));
				p.setShared(rs.getBoolean("shared"));
				p.setSourceId(rs.getInt("sourceid"));

				ps = connection.prepareStatement(query2);
				ps.setInt(1, p.getId());
				innerRs = ps.executeQuery();

				while (innerRs.next()) {
					Routine r = RoutineDB.selectRoutine(innerRs
							.getInt("routineid"));
					r.setProgramNote(innerRs.getString("note"));
					p.getRoutines().add(r);
				}

				programs.add(p);
			}

		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}
		return programs;
	}

	public static int insert(Program program, int userId) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		Statement s = null;
		ResultSet rs = null;

		String query = "insert into programs (name,description,author,creator_id,ownerid)"
				+ " values (?,?,?,?,?)";
		String query2 = "select last_insert_id()";

		try {
			ps = connection.prepareStatement(query);
			ps.setString(1, program.getName());
			ps.setString(2, program.getDescription());
			ps.setString(3, program.getAuthor());
			ps.setInt(4, userId);
			ps.setInt(5, userId);
			if (ps.executeUpdate() > 0) {
				s = connection.createStatement();
				rs = s.executeQuery(query2);
				rs.first();
				return (rs.getInt("last_insert_id()"));
			}
		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}
		return 0;

	}

	public static boolean removeRoutine(int programId, int routineId) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;

		String query = "delete from programentries where routineid = ? and programid = ?";

		try {
			ps = connection.prepareStatement(query);
			ps.setInt(1, routineId);
			ps.setInt(2, programId);

			if (ps.executeUpdate() > 0)
				return true;

		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}
		return false;
	}

	public static boolean update(int programId, String name, String description, String author, Boolean shared) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;

		String query = "update programs set name = ?,description = ?, author=?, shared=? where _id = ?";

		try {
			ps = connection.prepareStatement(query);
			ps.setString(1, name);
			ps.setString(2, description);
			ps.setString(3, author);
			ps.setBoolean(4, shared);
			ps.setInt(5, programId);
			
			if(ps.executeUpdate() > 0 )
				return true;

		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}
		return false;
	}

	public static boolean updateRoutine(int programId, int routineId,
			String note) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;

		String query = "update programentries set note = ? where programid=? and routineid = ?";

		try {
			ps = connection.prepareStatement(query);
			ps.setString(1, note);
			ps.setInt(2, programId);
			ps.setInt(3, routineId);
			if (ps.executeUpdate() > 0)
				return true;

		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}
		return false;
	}

}
